package dao.impl.CourseDaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import common.model.Entity.Semester;
import common.model.Entity.Student;
import common.model.Entity.courseEntity.Course;
import common.model.Entity.courseEntity.CourseSchedule;
import common.util.DBUtil;
import dao.CourseDao.CourseScheduleDao;
import dao.CourseDao.CourseDao;

/**
 * CourseDao 接口的实现类，提供课程信息的数据库操作。
 * 包括课程查询、添加、更新、删除、选课、退课等功能。
 * 使用 Apache Commons DbUtils 简化 JDBC 操作。
 */
public class CourseDaoImpl implements CourseDao {
    private static final Logger logger = LoggerFactory.getLogger(CourseDaoImpl.class);
    private final QueryRunner queryRunner = new QueryRunner();
    private final CourseScheduleDao courseScheduleDao;

    /**
     * 构造方法，初始化 CourseScheduleDao 实例。
     */
    public CourseDaoImpl() {
        courseScheduleDao = new CourseScheduleDaoImpl();
    }

    /**
     * 根据课程ID查询课程信息。
     *
     * @param id 课程ID
     * @return 返回包含课程的 Optional，如果课程不存在返回 Optional.empty()
     */
    @Override
    public Optional<Course> findById(Long id) {
        String sql = """
            SELECT
                id, course_code AS courseCode, course_name AS courseName,
                teacher_id AS teacherId, credit, max_capacity AS maxCapacity, week_start AS weekStart, week_end AS weekEnd,
                current_enrollment AS currentEnrollment, semester, description,class_period AS classPeriod,
                status, created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course
            WHERE id = ? AND is_deleted = 0
            """;

        try (Connection conn = DBUtil.getConnection()) {
            Course course = queryRunner.query(conn, sql, new BeanHandler<>(Course.class), id);
            return Optional.ofNullable(course);
        } catch (SQLException e) {
            logger.error("根据ID查询课程失败: id={}", id, e);
            return Optional.empty();
        }
    }

    /**
     * 根据课程ID查询课程及其排课信息。
     *
     * @param id 课程ID
     * @return 返回包含课程及排课信息的 Optional，如果课程不存在返回 Optional.empty()
     */
    @Override
    public Optional<Course> findByIdWithSchedules(Long id) {
        String courseSql = """
        SELECT
            id, course_code AS courseCode, course_name AS courseName,
            teacher_id AS teacherId, credit, max_capacity AS maxCapacity, week_start AS weekStart, week_end AS weekEnd,
            current_enrollment AS currentEnrollment, semester, description,class_period AS classPeriod,
            status, created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
        FROM t_course
        WHERE id = ? AND is_deleted = 0
        """;

        try (Connection conn = DBUtil.getConnection()) {
            Course course = queryRunner.query(conn, courseSql, new BeanHandler<>(Course.class), id);
            if (course == null) {
                return Optional.empty();
            }

            List<CourseSchedule> schedules = courseScheduleDao.findByCourseId(id);
            course.setSchedules(schedules);
            return Optional.of(course);
        } catch (SQLException e) {
            logger.error("根据ID查询课程及排课信息失败: id={}", id, e);
            return Optional.empty();
        }
    }

    /**
     * 查询所有未删除的课程。
     *
     * @return 返回课程列表，如果查询失败返回空列表
     */
    @Override
    public List<Course> findAll() {
        String sql = """
            SELECT
                id, course_code AS courseCode, course_name AS courseName,
                teacher_id AS teacherId, credit, max_capacity AS maxCapacity, week_start AS weekStart, week_end AS weekEnd,
                current_enrollment AS currentEnrollment, semester, description,class_period AS classPeriod,
                status, created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course
            WHERE is_deleted = 0
            ORDER BY created_at DESC
            """;

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Course.class));
        } catch (SQLException e) {
            logger.error("查询所有课程失败", e);
            return new ArrayList<>();
        }
    }

    /**
     * 查询所有课程及其排课信息。
     *
     * @return 返回课程及排课信息列表，如果查询失败返回空列表
     */
    @Override
    public List<Course> findAllWithSchedules() {
        String courseSql = """
            SELECT
                id, course_code AS courseCode, course_name AS courseName,
                teacher_id AS teacherId, credit, max_capacity AS maxCapacity, week_start AS weekStart, week_end AS weekEnd,
                current_enrollment AS currentEnrollment, semester, description,class_period AS classPeriod,
                status, created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course
            WHERE is_deleted = 0
            ORDER BY created_at DESC
            """;

        try (Connection conn = DBUtil.getConnection()) {
            List<Course> courses = queryRunner.query(conn, courseSql, new BeanListHandler<>(Course.class));
            if (courses.isEmpty()) {
                return courses;
            }

            for (Course course : courses) {
                Long curId = course.getId();
                List<CourseSchedule> lst = courseScheduleDao.findByCourseId(curId);
                course.setSchedules(lst);
            }

            return courses;
        } catch (SQLException e) {
            logger.error("查询所有课程及排课信息失败", e);
            return new ArrayList<>();
        }
    }

    /**
     * 根据学期查询课程。
     *
     * @param semester 学期
     * @return 返回该学期的课程列表，如果查询失败返回空列表
     */
    @Override
    public List<Course> findBySemester(Semester semester) {
        String sm = semester.toString();
        String sql = """
            SELECT
                id, course_code AS courseCode, course_name AS courseName,
                teacher_id AS teacherId, credit, max_capacity AS maxCapacity, week_start AS weekStart, week_end AS weekEnd,
                current_enrollment AS currentEnrollment, semester, description,class_period AS classPeriod,
                status, created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course
            WHERE semester = ? AND is_deleted = 0
            ORDER BY created_at DESC
            """;

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Course.class), sm);
        } catch (SQLException e) {
            logger.error("根据学期查询课程失败: semester={}", sm, e);
            return new ArrayList<>();
        }
    }

    /**
     * 根据教师姓名查询课程。
     *
     * @param teacherName 教师姓名（模糊查询）
     * @return 返回教师所授课程列表，如果查询失败返回空列表
     */
    @Override
    public List<Course> findByTeacherName(String teacherName) {
        String sql = """
        SELECT
            c.id, c.course_code AS courseCode, c.course_name AS courseName,
            c.teacher_id AS teacherId, c.credit, c.max_capacity AS maxCapacity, 
            c.week_start AS weekStart, c.week_end AS weekEnd,
            c.current_enrollment AS currentEnrollment, c.semester, c.description,
            c.class_period AS classPeriod, c.status, c.created_at AS createdAt, 
            c.updated_at AS updatedAt, c.is_deleted AS isDeleted
        FROM t_course c
        INNER JOIN t_user u ON c.teacher_id = u.id
        WHERE u.username LIKE CONCAT('%', ?, '%') 
            AND c.is_deleted = 0
            AND u.is_deleted = 0
        ORDER BY c.created_at DESC
        """;

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Course.class), teacherName);
        } catch (SQLException e) {
            logger.error("根据教师姓名查询课程失败: teacherName={}", teacherName, e);
            return new ArrayList<>();
        }
    }

    /**
     * 根据课程编码查询课程。
     *
     * @param CourseCode 课程编码
     * @return 返回对应课程列表，如果查询失败返回空列表
     */
    @Override
    public List<Course> findByCourseCode(String CourseCode) {
        String sql = """
            SELECT
                id, course_code AS courseCode, course_name AS courseName,
                teacher_id AS teacherId, credit, max_capacity AS maxCapacity, week_start AS weekStart, week_end AS weekEnd,
                current_enrollment AS currentEnrollment, semester, description,class_period AS classPeriod,
                status, created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course
            WHERE course_code = ? AND is_deleted = 0
            ORDER BY created_at DESC
            """;

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Course.class), CourseCode);
        } catch (SQLException e) {
            logger.error("根据课程编码查询课程失败: CourseCode={}", CourseCode, e);
            return new ArrayList<>();
        }
    }

    /**
     * 根据课程名称查询课程。
     *
     * @param CourseName 课程名称
     * @return 返回对应课程列表，如果查询失败返回空列表
     */
    @Override
    public List<Course> findByCourseName(String CourseName) {
        String sql = """
            SELECT
                id, course_code AS courseCode, course_name AS courseName,
                teacher_id AS teacherId, credit, max_capacity AS maxCapacity, week_start AS weekStart, week_end AS weekEnd,
                current_enrollment AS currentEnrollment, semester, description,class_period AS classPeriod,
                status, created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course
            WHERE course_name = ? AND is_deleted = 0
            ORDER BY created_at DESC
            """;

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Course.class), CourseName);
        } catch (SQLException e) {
            logger.error("根据课程名称查询课程失败: CourseName={}", CourseName, e);
            return new ArrayList<>();
        }
    }

    /**
     * 保存课程到数据库。
     *
     * @param course 课程实体
     * @return 返回是否保存成功
     */
    @Override
    public boolean save(Course course) {
        String sql = """
        INSERT INTO t_course
            (course_code, course_name, teacher_id, credit, max_capacity, week_start, week_end, class_period ,
             current_enrollment, semester, description, status, created_at, is_deleted)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """;

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            stmt.setString(1, course.getCourseCode());
            stmt.setString(2, course.getCourseName());
            stmt.setObject(3, course.getTeacherId());
            stmt.setInt(4, course.getCredit());
            stmt.setInt(5, course.getMaxCapacity());
            stmt.setInt(6, course.getWeekStart());
            stmt.setInt(7, course.getWeekEnd());
            stmt.setInt(8, course.getClassPeriod());
            stmt.setInt(9, course.getCurrentEnrollment() != null ? course.getCurrentEnrollment() : 0);
            stmt.setString(10, course.getSemester());
            stmt.setString(11, course.getDescription());
            stmt.setInt(12, course.getStatus() != null ? course.getStatus() : 0);
            stmt.setObject(13, LocalDateTime.now());
            stmt.setInt(14, course.getIsDeleted() != null ? course.getIsDeleted() : 0);

            int affectedRows = stmt.executeUpdate();

            if (affectedRows > 0) {
                try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        Long generatedId = generatedKeys.getLong(1);
                        course.setId(generatedId);
                        return true;
                    }
                }
            }
            return false;

        } catch (SQLException e) {
            logger.error("保存课程失败: {}", course, e);
            return false;
        }
    }

    /**
     * 更新课程信息。
     *
     * @param course 课程实体
     * @return 返回更新是否成功
     */
    public boolean update(Course course) {
        Optional<Course> existingCourse = findById(course.getId());
        if (existingCourse.isEmpty()) {
            throw new RuntimeException("课程不存在，ID: " + course.getId());
        }

        String updateSql = """
        UPDATE t_course SET
            course_code = ?, course_name = ?, teacher_id = ?,
            credit = ?, max_capacity = ?, week_start = ? , week_end = ? ,
            class_period = ? , current_enrollment = ?,
            semester = ?, description = ?, status = ?,
            updated_at = ?
        WHERE id = ? AND is_deleted = 0
        """;

        try (Connection conn = DBUtil.getConnection()) {
            int affectedRows = queryRunner.update(conn, updateSql,
                    course.getCourseCode(),
                    course.getCourseName(),
                    course.getTeacherId(),
                    course.getCredit(),
                    course.getMaxCapacity(),
                    course.getWeekStart(),
                    course.getWeekEnd(),
                    course.getClassPeriod(),
                    course.getCurrentEnrollment(),
                    course.getSemester() != null ? course.getSemester() : null,
                    course.getDescription(),
                    course.getStatus(),
                    LocalDateTime.now(),
                    course.getId()
            );

            return affectedRows > 0;
        } catch (SQLException e) {
            logger.error("更新课程失败: ID={}", course.getId(), e);
            throw new RuntimeException("更新课程失败: " + e.getMessage(), e);
        }
    }

    /**
     * 根据课程ID逻辑删除课程。
     *
     * @param id 课程ID
     * @return 返回删除是否成功
     */
    @Override
    public boolean deleteById(Long id) {
        String sql = "UPDATE t_course SET is_deleted = 1, updated_at = ? WHERE id = ?";

        try (Connection conn = DBUtil.getConnection()) {
            int affectedRows = queryRunner.update(conn, sql, LocalDateTime.now(), id);
            return affectedRows > 0;
        } catch (SQLException e) {
            logger.error("删除课程失败: id={}", id, e);
            return false;
        }
    }

    /**
     * 增加课程选课人数，当前人数小于最大容量时生效。
     *
     * @param courseId 课程ID
     * @return 返回是否增加成功
     */
    @Override
    public boolean incrementEnrollment(Long courseId) {
        String sql = """
            UPDATE t_course
            SET current_enrollment = current_enrollment + 1, updated_at = ?
            WHERE id = ?
              AND current_enrollment < max_capacity
        """;

        try (Connection conn = DBUtil.getConnection()) {
            int affectedRows = queryRunner.update(conn, sql, LocalDateTime.now(), courseId);
            return affectedRows > 0;
        } catch (SQLException e) {
            logger.error("增加课程选课人数失败: courseId={}", courseId, e);
            return false;
        }
    }

    /**
     * 减少课程选课人数，当前人数大于0时生效。
     *
     * @param courseId 课程ID
     * @return 返回是否减少成功
     */
    @Override
    public boolean decrementEnrollment(Long courseId) {
        String sql = """
            UPDATE t_course
            SET current_enrollment = current_enrollment - 1, updated_at = ?
            WHERE id = ? AND is_deleted = 0
            AND current_enrollment > 0
            """;

        try (Connection conn = DBUtil.getConnection()) {
            int affectedRows = queryRunner.update(conn, sql, LocalDateTime.now(), courseId);
            return affectedRows > 0;
        } catch (SQLException e) {
            logger.error("减少课程选课人数失败: courseId={}", courseId, e);
            return false;
        }
    }

    /**
     * 统计课程数量。
     *
     * @return 返回课程总数
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_course WHERE is_deleted = 0";

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new ScalarHandler<>());
        } catch (SQLException e) {
            logger.error("统计课程数量失败", e);
            return 0;
        }
    }

    /**
     * 查询学生是否已选该课程。
     *
     * @param courseId 课程ID
     * @param studentId 学生ID
     * @return 已选返回 true，否则 false
     */
    @Override
    public boolean isStudentEnrolled(Long courseId, Long studentId) {
        String sql = "SELECT COUNT(*) FROM t_student_course WHERE course_id = ? AND student_id = ? AND is_deleted = 0 AND status = 1";
        try (Connection conn = DBUtil.getConnection()) {
            Long count = queryRunner.query(conn, sql, new ScalarHandler<>(), courseId, studentId);
            return count != null && count > 0;
        } catch (SQLException e) {
            logger.error("查询学生是否已选该课程失败: courseId={}, studentId={}", courseId, studentId, e);
            return false;
        }
    }

    /**
     * 学生选课操作，如果已存在记录则更新，否则插入新记录。
     *
     * @param courseId 课程ID
     * @param studentId 学生ID
     * @return 返回选课是否成功
     */
    @Override
    public boolean enrollStudentInCourseTable(Long courseId, Long studentId) {
        String checkSql = """
        SELECT id, is_deleted, status
        FROM t_student_course
        WHERE student_id = ? AND course_id = ?
        """;

        String updateSql = """
        UPDATE t_student_course
        SET status = 1, is_deleted = 0, selected_at = ?, updated_at = ?
        WHERE id = ?
        """;

        String insertSql = """
        INSERT INTO t_student_course (student_id, course_id, status, selected_at, created_at, is_deleted)
        VALUES (?, ?, 1, ?, ?, 0)
        """;

        try (Connection conn = DBUtil.getConnection()) {
            try (PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
                checkStmt.setLong(1, studentId);
                checkStmt.setLong(2, courseId);

                try (ResultSet rs = checkStmt.executeQuery()) {
                    if (rs.next()) {
                        Long recordId = rs.getLong("id");
                        try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
                            LocalDateTime now = LocalDateTime.now();
                            updateStmt.setObject(1, now);
                            updateStmt.setObject(2, now);
                            updateStmt.setLong(3, recordId);

                            int affectedRows = updateStmt.executeUpdate();
                            return affectedRows > 0;
                        }
                    } else {
                        try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                            LocalDateTime now = LocalDateTime.now();
                            insertStmt.setLong(1, studentId);
                            insertStmt.setLong(2, courseId);
                            insertStmt.setObject(3, now);
                            insertStmt.setObject(4, now);

                            int affectedRows = insertStmt.executeUpdate();
                            return affectedRows > 0;
                        }
                    }
                }
            }
        } catch (SQLException e) {
            logger.error("学生选课失败: courseId={}, studentId={}", courseId, studentId, e);
            return false;
        }
    }

    /**
     * 学生退课操作。
     *
     * @param courseId 课程ID
     * @param studentId 学生ID
     * @return 返回退课是否成功
     */
    @Override
    public boolean dropStudentFromCourseTable(Long courseId, Long studentId) {
        String sql = """
        UPDATE t_student_course
        SET status = 0, is_deleted = 1, updated_at = ?
        WHERE course_id = ? AND student_id = ? AND is_deleted = 0
        """;
        try (Connection conn = DBUtil.getConnection()) {
            int affectedRows = queryRunner.update(conn, sql, LocalDateTime.now(), courseId, studentId);
            return affectedRows > 0;
        } catch (SQLException e) {
            logger.error("学生退课失败: courseId={}, studentId={}", courseId, studentId, e);
            return false;
        }
    }

    /**
     * 根据课程ID查询已选该课程的学生列表。
     *
     * @param courseId 课程ID
     * @return 返回学生列表，如果查询失败返回空列表
     */
    @Override
    public List<Student> findStudentsByCourseId(Long courseId) {
        String sql = """
            SELECT s.*
            FROM t_student_course sc
            JOIN t_student s ON sc.student_id = s.id
            WHERE sc.course_id = ? AND sc.is_deleted = 0 AND sc.status = 1
            """;
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Student.class), courseId);
        } catch (SQLException e) {
            logger.error("根据课程ID查询选课学生失败: courseId={}", courseId, e);
            return new ArrayList<>();
        }
    }

    /**
     * 查询所有启用状态的课程。
     *
     * @return 返回课程列表，如果查询失败返回空列表
     */
    @Override
    public List<Course> findStatusTrue() {
        String sql = """
            SELECT
                id, course_code AS courseCode, course_name AS courseName,
                teacher_id AS teacherId, credit, max_capacity AS maxCapacity, week_start AS weekStart, week_end AS weekEnd,
                current_enrollment AS currentEnrollment, semester, description,class_period AS classPeriod,
                status, created_at AS createdAt, updated_at AS updatedAt, is_deleted AS isDeleted
            FROM t_course
            WHERE is_deleted = 0 AND status = 1
            ORDER BY created_at DESC
            """;

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Course.class));
        } catch (SQLException e) {
            logger.error("查询所有课程失败", e);
            return new ArrayList<>();
        }
    }

    /**
     * 根据学生ID查询已选课程。
     *
     * @param studentId 学生ID
     * @return 返回已选课程列表，如果查询失败返回空列表
     */
    @Override
    public List<Course> findSelectedCourseByStudentId(Long studentId) {
        String sql = """
        SELECT
            c.id, c.course_code AS courseCode, c.course_name AS courseName,
            c.teacher_id AS teacherId, c.credit, c.max_capacity AS maxCapacity,
            c.week_start AS weekStart, c.week_end AS weekEnd,
            c.current_enrollment AS currentEnrollment, c.semester, c.description,
            c.class_period AS classPeriod, c.status, c.created_at AS createdAt,
            c.updated_at AS updatedAt, c.is_deleted AS isDeleted
        FROM t_course c
        INNER JOIN t_student_course sc ON c.id = sc.course_id
        WHERE sc.student_id = ?
            AND sc.is_deleted = 0
            AND sc.status = 1
            AND c.is_deleted = 0
        ORDER BY c.created_at DESC
        """;
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Course.class), studentId);
        } catch (SQLException e) {
            logger.error("根据学生ID查询已选课程失败: studentId={}", studentId, e);
            return new ArrayList<>();
        }
    }

    /**
     * 查询需要排课的课程列表。
     *
     * @return 返回待排课课程列表，如果查询失败返回空列表
     */
    @Override
    public List<Course> findCoursesToSchedule() {
        String sql = """
        SELECT
            c.id, c.course_code AS courseCode, c.course_name AS courseName,
            c.teacher_id AS teacherId, c.credit, c.max_capacity AS maxCapacity,
            c.week_start AS weekStart, c.week_end AS weekEnd,
            c.current_enrollment AS currentEnrollment, c.semester, c.description,
            c.class_period AS classPeriod, c.status, c.created_at AS createdAt,
            c.updated_at AS updatedAt, c.is_deleted AS isDeleted
        FROM t_course c
        WHERE c.is_deleted =0 AND c.status = 0
        """;

        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Course.class));
        } catch (SQLException e) {
            logger.error("查找需要排课的课程失败", e);
            return new ArrayList<>();
        }
    }
}
